package org.lq.shopping.dao.impl;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.List;


import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.log4j.Logger;
import org.lq.shopping.dao.shop_expressDao;
import org.lq.shopping.entity.ShopExpress;
import org.lq.shopping.util.DruidUtil;

public class shop_expressDaoImpl implements shop_expressDao{
   QueryRunner qr=new QueryRunner(DruidUtil.getDataSource());
   Logger log=Logger.getLogger(shop_expressDaoImpl.class);
	
	

	@Override
	public List<ShopExpress> findAll() {
	    String sql="select * from shop_express";
	    log.info("SQL语句:[" + sql + "]");
	    List<ShopExpress> list=null;
	    try {
			list=qr.query(sql, new BeanListHandler<>(ShopExpress.class));
		} catch (SQLException e) {
			log.error(e);
		}
	    
	 
		return list;
	}

	@Override
	public ShopExpress findById(int id) {
		String sql="select * from shop_express where express_id=?";
		log.info("SQL语句:[" + sql + "]");
		ShopExpress shopExpress =null;
		try {
			shopExpress=qr.query(sql, new BeanHandler<>(ShopExpress.class),id);
		} catch (SQLException e) {
			log.error(e);
		}
		return shopExpress;
	}

	@Override
	public int totalCount() {
		int count=0;
		String sql="select count(1) from shop_express";
		log.info("SQL语句:[" + sql + "]");
		try {
		
		  BigDecimal bd=qr.query(sql, new ScalarHandler<BigDecimal>());
			count=bd.intValue();
		} catch (SQLException e) {
			log.error(e);
		}
		return count;
	}

	@Override
	public List<ShopExpress> findPage(int pageIndex, int pageSize) {
		String sql="select * from("
				+"select rownum r,t.* from("
				+"select * from shop_express order by express_id"
				+ ") t where rownum<= ? "
				+") where r>?";
		log.info("SQL语句:[" + sql + "]");
		log.debug("传入参数:[" + pageIndex+"-----"+pageSize + "]");
		int max=pageIndex * pageSize;
		int min= (pageIndex-1)* pageSize;
		
		List<ShopExpress> list =null;
		try {
			list=qr.query(sql, new BeanListHandler<>(ShopExpress.class),max,min);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return list;
	}
	
	
	
	
	
	@Override
	public int add(ShopExpress t) {
		int num = 0;
		String sql = "insert into shop_express" + 
				"  (express_type, express_name, express_phone, express_cost, order_id, express_remark, express_id)" + 
				"  values" + 
				"  (?,?,?,?,?,?,EXPRESS_SEQ.nextval)" ; 
		log.debug("SQL语句:[" + sql + "]");
		log.debug("传入参数:[" + t + "]");

		try {
			num = qr.execute(sql, t.getExpress_type(),t.getExpress_name(),t.getExpress_phone()
					,t.getExpress_cost(),t.getOrder_id(),t.getExpress_remark());
		} catch (SQLException e) {
			
			log.error(e);
			
		}
		return num;
	}

	@Override
	public int delete(int id) {
		int num = 0;
		String sql = "delete shop_express where express_id = ?";
		log.debug("SQL语句:[" + sql + "]");
		log.debug("传入参数:[" + id + "]");
		try {
			num = qr.execute(sql, id);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			log.error(e);
		}
		return num;
	}

	@Override
	public int update(ShopExpress t) {
		int num = 0;
		String sql = "update shop_express" + 
				"                  set express_type = ?," + 
				"                      express_name = ?," + 
				"                      express_phone = ?," + 
				"                      express_cost = ?," + 
				"                      order_id = ?," + 
				"                      express_remark = ?," + 
				"                where express_id = ?";
		log.debug("SQL语句:[" + sql + "]");
		log.debug("传入参数:[" + t + "]");
		try {
			num = qr.execute(sql, t);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			log.error(e);
		}
		return num;
	}
	
	

	@Override
	public List<ShopExpress> LikeColumnByValue(String columName, Object value) {
		List<ShopExpress> list = null;
		String sql = "select * from shop_express where "+ columName +"=?";
		log.debug("SQL语句:[" + sql + "]");
		log.debug("传入参数:[" + columName + value + "]");
		try {
			list = qr.query(sql, new BeanListHandler<>(ShopExpress.class),value);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			log.error(e);
		}
		return list;
	}

	
	
	
	@Override
	public int getCount() {
		int num=0;
		String sql ="select count(1) from shop_express";
		log.debug("SQL语句:[" + sql + "]");
		try {
			num = qr.query(sql, new ScalarHandler<BigDecimal>()).intValue();
		} catch (SQLException e) {
			log.error(e);
		}
		return num;
	}

}
